{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Hull-White","README"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":12,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Hull-White","index":0,"n_rows":50,"n_cols":27,"rows_kept":50,"cut":false,"hidden":false,"merged":[]}
["Hull-White Tree",null,null,"Key:",null,"Time",0,null,{"$f":"G1+deltat","v":1},null,{"$f":"I1+deltat","v":2},null,{"$f":"K1+deltat","v":3},null,4,null,5,null,6,null,7,null,8,null,9,null,10]
["sigma",0.01,null,null,"Derivative Price","Zero Rates",null,null,0.04,null,0.05,null,0.06,null,0.06]
["a",0,null,null,"State Price","ZCB Price",{"$f":"EXP(-G$2*G$1)","v":1},null,{"$f":"EXP(-I$2*I$1)","v":0.96078943915232318},null,{"$f":"EXP(-K$2*K$1)","v":0.90483741803595952},null,{"$f":"EXP(-M$2*M$1)","v":0.835270211411272},null,{"$f":"EXP(-O$2*O$1)","v":0.78662786106655347}]
["deltat",1,null,null,"Short Rate","Calculated Price",null,null,{"$f":"G48*EXP(-G49*deltat)+G43*EXP(-G44*deltat)+G38*EXP(-G39*deltat)+G33*EXP(-G34*deltat)+G28*EXP(-G29*deltat)+G23*EXP(-G24*deltat)+G18*EXP(-G19*deltat)","v":0.96078943915232273},null,{"$f":"I48*EXP(-I49*deltat)+I43*EXP(-I44*deltat)+I38*EXP(-I39*deltat)+I33*EXP(-I34*deltat)+I28*EXP(-I29*deltat)+I23*EXP(-I24*deltat)+I18*EXP(-I19*deltat)","v":0.90483741803595907},null,{"$f":"K48*EXP(-K49*deltat)+K43*EXP(-K44*deltat)+K38*EXP(-K39*deltat)+K33*EXP(-K34*deltat)+K28*EXP(-K29*deltat)+K23*EXP(-K24*deltat)+K18*EXP(-K19*deltat)","v":0.83527021140616853},null,{"$f":"M48*EXP(-M49*deltat)+M43*EXP(-M44*deltat)+M38*EXP(-M39*deltat)+M33*EXP(-M34*deltat)+M28*EXP(-M29*deltat)+M23*EXP(-M24*deltat)+M18*EXP(-M19*deltat)","v":0.78662786934794537}]
["deltaR",{"$f":"sigma*SQRT(3*deltat)","v":1.7320508075688773E-2},null,null,null,"Pricing Error",null,null,{"$f":"I4-I3","v":0},null,{"$f":"K4-K3","v":0},null,{"$f":"M4-M3","v":-5.1034731995969196E-12},null,{"$f":"O4-O3","v":8.2813919055624297E-9}]
["jmax",{"$f":"IF(a=0,1000,0.184/(a*deltat))","v":1000},null,null,"Transition","new alpha",{"$f":"G7+I5","v":4.000000000000048E-2},null,{"$f":"I7+K5","v":6.004999999999161E-2},null,{"$f":"K7+M5","v":8.0200000000472671E-2},null,{"$f":"M7+O5","v":6.04499977157682E-2}]
["jmin",{"$f":"-jmax","v":-1000},null,null,"Probabilities ¯","alpha",{"$f":"G6","v":4.000000000000048E-2},null,{"$f":"I6","v":6.004999999999161E-2},null,{"$f":"K6","v":8.0200000000472671E-2},null,{"$f":"M6","v":6.04499977157682E-2}]
[]
[]
[]
[]
[]
[]
[]
[]
["Derivative payoff Formula:","MAX(bondprice-93,0)",null,null,{"$f":"IF(D18<=jmin,1/6+(1/2)*(a^2*D18^2*deltat^2+a*D18*deltat),0)","v":0},null,null,null,null,null,null,null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$19-93,0)"}]
["Use shortrate and/or bondprice as variables",null,null,null,{"$f":"IF(D18<=jmin,-1/3-a^2*D18^2*deltat^2-2*a*D18*deltat,IF(D18<jmax,1/6+(1/2)*(a^2*D18^2*deltat^2-a*D18*deltat),0))","v":0.16666666666666666},null,null,null,null,null,null,null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M20,MAX((O7*$E16+O12*$E17+O17*$E18+O22*$E19+O27*$E20)*EXP(-M19*deltat),IF(AmerEur=\"American\",M20,-1E+307))))","v":0}]
["Examples: MAX(bondprice-99,0) or 10000*MAX(.05-shortrate,0)",null,null,{"$f":"D23+1","v":3},{"$f":"IF(D18<=jmin,7/6+(1/2)*(a^2*D18^2*deltat^2+3*a*D18*deltat),IF(D18<jmax,2/3-a^2*D18^2*deltat^2,7/6+(1/2)*(a^2*D18^2*deltat^2-3*a*D18*deltat)))","v":0.66666666666666663},null,null,null,null,null,null,null,{"$f":"$E26*EXP(-K29*deltat)*K28+$E22*EXP(-K24*deltat)*K23+$E18*EXP(-K19*deltat)*K18+$E14*EXP(-K14*deltat)*K13+$E10*EXP(-K9*deltat)*K8","v":3.6702704339091493E-3}]
["American/European","European",null,null,{"$f":"IF(D18<=jmin,0,IF(D18<jmax,1/6+(1/2)*(a^2*D18^2*deltat^2+a*D18*deltat),-1/3-a^2*D18^2*deltat^2+2*a*D18*deltat))","v":0.16666666666666666},null,null,null,null,null,null,{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M19*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D18*deltaR+M$7","v":0.11241152194283452}]
["Derivative Maturity",2,null,null,{"$f":"IF(D18<jmax,0,1/6+(1/2)*(a^2*D18^2*deltat^2-a*D18*deltat))","v":0},null,null,null,null,null,null,null,{"$f":"payoff","v":7}]
["Zero Coupon Bond Maturity",3,null,null,{"$f":"IF(D23<=jmin,1/6+(1/2)*(a^2*D23^2*deltat^2+a*D23*deltat),0)","v":0},null,null,null,null,null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($J$24-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$24-93,0)"}]
["Zero Coupon Bond Face Value",100,null,null,{"$f":"IF(D23<=jmin,-1/3-a^2*D23^2*deltat^2-2*a*D23*deltat,IF(D23<jmax,1/6+(1/2)*(a^2*D23^2*deltat^2-a*D23*deltat),0))","v":0.16666666666666666},null,null,null,null,null,{"$f":"IF(K$1>derivativematurity,0,IF(K$1=derivativematurity,K25,MAX((M12*$E21+M17*$E22+M22*$E23+M27*$E24+M32*$E25)*EXP(-K24*deltat),IF(AmerEur=\"American\",K25,-1E+307))))","v":4.4259501256291855E-2},null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M25,MAX((O12*$E21+O17*$E22+O22*$E23+O27*$E24+O32*$E25)*EXP(-M24*deltat),IF(AmerEur=\"American\",M25,-1E+307))))","v":0}]
["Derivative Description:",null,null,{"$f":"D28+1","v":2},{"$f":"IF(D23<=jmin,7/6+(1/2)*(a^2*D23^2*deltat^2+3*a*D23*deltat),IF(D23<jmax,2/3-a^2*D23^2*deltat^2,7/6+(1/2)*(a^2*D23^2*deltat^2-3*a*D23*deltat)))","v":0.66666666666666663},null,null,null,null,null,{"$f":"$E31*EXP(-I34*deltat)*I33+$E27*EXP(-I29*deltat)*I28+$E23*EXP(-I24*deltat)*I23+$E19*EXP(-I19*deltat)*I18+$E15*EXP(-I14*deltat)*I13","v":2.4701545992145843E-2},null,{"$f":"$E31*EXP(-K34*$B$4)*K33+$E27*EXP(-K29*$B$4)*K28+$E23*EXP(-K24*$B$4)*K23+$E19*EXP(-K19*$B$4)*K18+$E15*EXP(-K14*$B$4)*K13","v":4.4817222772453431E-2}]
[null,"Call on Zero Coupon Bond",null,null,{"$f":"IF(D23<=jmin,0,IF(D23<jmax,1/6+(1/2)*(a^2*D23^2*deltat^2+a*D23*deltat),-1/3-a^2*D23^2*deltat^2+2*a*D23*deltat))","v":0.16666666666666666},null,null,null,null,{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(K$2,0,2*(BondMaturity-K$1)/deltat))/K$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)^2*IF(a=0,K$1/2,(1-EXP(-2*a*K$1))/(4*a))-IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)*((K24*deltat+LN(M$3/K$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,K$1*deltat/2,(1-EXP(-2*a*K$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":89.15078679875721},{"$f":"$D23*deltaR+K$7","v":0.11484101615185022},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M24*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D23*deltaR+M$7","v":9.5091013867145746E-2}]
[null,"Bond Matures in 3 Years",null,null,{"$f":"IF(D23<jmax,0,1/6+(1/2)*(a^2*D23^2*deltat^2-a*D23*deltat))","v":0},null,null,null,null,null,{"$f":"payoff","v":4.4259501256291855E-2},null,{"$f":"payoff","v":7}]
[null,"Option Expires in 2 Years",null,null,{"$f":"IF(D28<=jmin,1/6+(1/2)*(a^2*D28^2*deltat^2+a*D28*deltat),0)","v":0},null,null,null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($H$29-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($J$29-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$29-93,0)"}]
[null,"Bond Face Value 100",null,null,{"$f":"IF(D28<=jmin,-1/3-a^2*D28^2*deltat^2-2*a*D28*deltat,IF(D28<jmax,1/6+(1/2)*(a^2*D28^2*deltat^2-a*D28*deltat),0))","v":0.16666666666666666},null,null,null,{"$f":"IF(I$1>derivativematurity,0,IF(I$1=derivativematurity,I30,MAX((K17*$E26+K22*$E27+K27*$E28+K32*$E29+K37*$E30)*EXP(-I29*deltat),IF(AmerEur=\"American\",I30,-1E+307))))","v":1.1043403030113526},null,{"$f":"IF(K$1>derivativematurity,0,IF(K$1=derivativematurity,K30,MAX((M17*$E26+M22*$E27+M27*$E28+M32*$E29+M37*$E30)*EXP(-K29*deltat),IF(AmerEur=\"American\",K30,-1E+307))))","v":1.1908211933978095},null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M30,MAX((O17*$E26+O22*$E27+O27*$E28+O32*$E29+O37*$E30)*EXP(-M29*deltat),IF(AmerEur=\"American\",M30,-1E+307))))","v":0}]
[null,"Option Strike Price 93",null,{"$f":"D33+1","v":1},{"$f":"IF(D28<=jmin,7/6+(1/2)*(a^2*D28^2*deltat^2+3*a*D28*deltat),IF(D28<jmax,2/3-a^2*D28^2*deltat^2,7/6+(1/2)*(a^2*D28^2*deltat^2-3*a*D28*deltat)))","v":0.66666666666666663},null,null,null,{"$f":"$E36*EXP(-G39*deltat)*G38+$E32*EXP(-G34*deltat)*G33+$E28*EXP(-G29*deltat)*G28+$E24*EXP(-G24*deltat)*G23+$E20*EXP(-G19*deltat)*G18","v":0.16013157319205379},null,{"$f":"$E36*EXP(-I39*deltat)*I38+$E32*EXP(-I34*deltat)*I33+$E28*EXP(-I29*deltat)*I28+$E24*EXP(-I24*deltat)*I23+$E20*EXP(-I19*deltat)*I18","v":0.19933864811259366},null,{"$f":"$E36*EXP(-K39*deltat)*K38+$E32*EXP(-K34*deltat)*K33+$E28*EXP(-K29*deltat)*K28+$E24*EXP(-K24*deltat)*K23+$E20*EXP(-K19*deltat)*K18","v":0.1938044460805341}]
[null,null,null,null,{"$f":"IF(D28<=jmin,0,IF(D28<jmax,1/6+(1/2)*(a^2*D28^2*deltat^2+a*D28*deltat),-1/3-a^2*D28^2*deltat^2+2*a*D28*deltat))","v":0.16666666666666666},null,null,{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(I$2,0,2*(BondMaturity-I$1)/deltat))/I$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-I$1,(1-EXP(-a*(BondMaturity-I$1)))/a)^2*IF(a=0,I$1/2,(1-EXP(-2*a*I$1))/(4*a))-IF(a=0,BondMaturity-I$1,(1-EXP(-a*(BondMaturity-I$1)))/a)*((I29*deltat+LN(K$3/I$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,I$1*deltat/2,(1-EXP(-2*a*I$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":83.959049148669962},{"$f":"$D28*deltaR+I$7","v":7.7370508075680383E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(K$2,0,2*(BondMaturity-K$1)/deltat))/K$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)^2*IF(a=0,K$1/2,(1-EXP(-2*a*K$1))/(4*a))-IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)*((K29*deltat+LN(M$3/K$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,K$1*deltat/2,(1-EXP(-2*a*K$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":90.708373881801947},{"$f":"$D28*deltaR+K$7","v":9.7520508076161444E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M29*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D28*deltaR+M$7","v":7.7770505791456973E-2}]
[null,null,null,null,{"$f":"IF(D28<jmax,0,1/6+(1/2)*(a^2*D28^2*deltat^2-a*D28*deltat))","v":0},null,null,null,{"$f":"payoff","v":0},null,{"$f":"payoff","v":1.1908211933978095},null,{"$f":"payoff","v":7}]
[null,null,null,null,{"$f":"IF(D33<=jmin,1/6+(1/2)*(a^2*D33^2*deltat^2+a*D33*deltat),0)","v":0},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($F$34-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($H$34-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($J$34-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$34-93,0)"}]
[null,null,null,null,{"$f":"IF(D33<=jmin,-1/3-a^2*D33^2*deltat^2-2*a*D33*deltat,IF(D33<jmax,1/6+(1/2)*(a^2*D33^2*deltat^2-a*D33*deltat),0))","v":0.16666666666666666},null,{"$f":"IF(G$1>derivativematurity,0,IF(G$1=derivativematurity,G35,MAX((I22*$E31+I27*$E32+I32*$E33+I37*$E34+I42*$E35)*EXP(-G34*deltat),IF(AmerEur=\"American\",G35,-1E+307))))","v":2.138151019909563},null,{"$f":"IF(I$1>derivativematurity,0,IF(I$1=derivativematurity,I35,MAX((K22*$E31+K27*$E32+K32*$E33+K37*$E34+K42*$E35)*EXP(-I34*deltat),IF(AmerEur=\"American\",I35,-1E+307))))","v":2.2167044624163195},null,{"$f":"IF(K$1>derivativematurity,0,IF(K$1=derivativematurity,K35,MAX((M22*$E31+M27*$E32+M32*$E33+M37*$E34+M42*$E35)*EXP(-K34*deltat),IF(AmerEur=\"American\",K35,-1E+307))))","v":2.3515116853270115},null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M35,MAX((O22*$E31+O27*$E32+O32*$E33+O37*$E34+O42*$E35)*EXP(-M34*deltat),IF(AmerEur=\"American\",M35,-1E+307))))","v":0}]
[null,null,null,0,{"$f":"IF(D33<=jmin,7/6+(1/2)*(a^2*D33^2*deltat^2+3*a*D33*deltat),IF(D33<jmax,2/3-a^2*D33^2*deltat^2,7/6+(1/2)*(a^2*D33^2*deltat^2-3*a*D33*deltat)))","v":0.66666666666666663},null,1,null,{"$f":"$E41*EXP(-G44*deltat)*G43+$E37*EXP(-G39*deltat)*G38+$E33*EXP(-G34*deltat)*G33+$E29*EXP(-G29*deltat)*G28+$E25*EXP(-G24*deltat)*G23","v":0.64052629276821516},null,{"$f":"$E41*EXP(-I44*deltat)*I43+$E37*EXP(-I39*deltat)*I38+$E33*EXP(-I34*deltat)*I33+$E29*EXP(-I29*deltat)*I28+$E25*EXP(-I24*deltat)*I23","v":0.45240362877135742},null,{"$f":"$E41*EXP(-K44*deltat)*K43+$E37*EXP(-K39*deltat)*K38+$E33*EXP(-K34*deltat)*K33+$E29*EXP(-K29*deltat)*K28+$E25*EXP(-K24*deltat)*K23","v":0.34023804559787024}]
[null,null,null,null,{"$f":"IF(D33<=jmin,0,IF(D33<jmax,1/6+(1/2)*(a^2*D33^2*deltat^2+a*D33*deltat),-1/3-a^2*D33^2*deltat^2+2*a*D33*deltat))","v":0.16666666666666666},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(G$2,0,2*(BondMaturity-G$1)/deltat))/G$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-G$1,(1-EXP(-a*(BondMaturity-G$1)))/a)^2*IF(a=0,G$1/2,(1-EXP(-2*a*G$1))/(4*a))-IF(a=0,BondMaturity-G$1,(1-EXP(-a*(BondMaturity-G$1)))/a)*((G34*deltat+LN(I$3/G$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,G$1*deltat/2,(1-EXP(-2*a*G$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":83.527021141127079},{"$f":"$D33*deltaR+G$7","v":4.000000000000048E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(I$2,0,2*(BondMaturity-I$1)/deltat))/I$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-I$1,(1-EXP(-a*(BondMaturity-I$1)))/a)^2*IF(a=0,I$1/2,(1-EXP(-2*a*I$1))/(4*a))-IF(a=0,BondMaturity-I$1,(1-EXP(-a*(BondMaturity-I$1)))/a)*((I34*deltat+LN(K$3/I$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,I$1*deltat/2,(1-EXP(-2*a*I$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":86.918438113774627},{"$f":"$D33*deltaR+I$7","v":6.004999999999161E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(K$2,0,2*(BondMaturity-K$1)/deltat))/K$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)^2*IF(a=0,K$1/2,(1-EXP(-2*a*K$1))/(4*a))-IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)*((K34*deltat+LN(M$3/K$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,K$1*deltat/2,(1-EXP(-2*a*K$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":92.293174157330824},{"$f":"$D33*deltaR+K$7","v":8.0200000000472671E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M34*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D33*deltaR+M$7","v":6.04499977157682E-2}]
[null,null,null,null,{"$f":"IF(D33<jmax,0,1/6+(1/2)*(a^2*D33^2*deltat^2-a*D33*deltat))","v":0},null,{"$f":"payoff","v":0},null,{"$f":"payoff","v":0},null,{"$f":"payoff","v":2.3515116853270115},null,{"$f":"payoff","v":7}]
[null,null,null,null,{"$f":"IF(D38<=jmin,1/6+(1/2)*(a^2*D38^2*deltat^2+a*D38*deltat),0)","v":0},null,null,null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($H$39-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($J$39-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$39-93,0)"}]
[null,null,null,null,{"$f":"IF(D38<=jmin,-1/3-a^2*D38^2*deltat^2-2*a*D38*deltat,IF(D38<jmax,1/6+(1/2)*(a^2*D38^2*deltat^2-a*D38*deltat),0))","v":0.16666666666666666},null,null,null,{"$f":"IF(I$1>derivativematurity,0,IF(I$1=derivativematurity,I40,MAX((K27*$E36+K32*$E37+K37*$E38+K42*$E39+K47*$E40)*EXP(-I39*deltat),IF(AmerEur=\"American\",I40,-1E+307))))","v":3.3813055575572788},null,{"$f":"IF(K$1>derivativematurity,0,IF(K$1=derivativematurity,K40,MAX((M27*$E36+M32*$E37+M37*$E38+M42*$E39+M47*$E40)*EXP(-K39*deltat),IF(AmerEur=\"American\",K40,-1E+307))))","v":3.5265050827939746},null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M40,MAX((O27*$E36+O32*$E37+O37*$E38+O42*$E39+O47*$E40)*EXP(-M39*deltat),IF(AmerEur=\"American\",M40,-1E+307))))","v":0}]
[null,null,null,{"$f":"D33-1","v":-1},{"$f":"IF(D38<=jmin,7/6+(1/2)*(a^2*D38^2*deltat^2+3*a*D38*deltat),IF(D38<jmax,2/3-a^2*D38^2*deltat^2,7/6+(1/2)*(a^2*D38^2*deltat^2-3*a*D38*deltat)))","v":0.66666666666666663},null,null,null,{"$f":"$E46*EXP(-G49*deltat)*G48+$E42*EXP(-G44*deltat)*G43+$E38*EXP(-G39*deltat)*G38+$E34*EXP(-G34*deltat)*G33+$E30*EXP(-G29*deltat)*G28","v":0.16013157319205379},null,{"$f":"$E46*EXP(-I49*deltat)*I48+$E42*EXP(-I44*deltat)*I43+$E38*EXP(-I39*deltat)*I38+$E34*EXP(-I34*deltat)*I33+$E30*EXP(-I29*deltat)*I28","v":0.20282136895669178},null,{"$f":"$E46*EXP(-K49*deltat)*K48+$E42*EXP(-K44*deltat)*K43+$E38*EXP(-K39*deltat)*K38+$E34*EXP(-K34*deltat)*K33+$E30*EXP(-K29*deltat)*K28","v":0.2006356661212893}]
[null,null,null,null,{"$f":"IF(D38<=jmin,0,IF(D38<jmax,1/6+(1/2)*(a^2*D38^2*deltat^2+a*D38*deltat),-1/3-a^2*D38^2*deltat^2+2*a*D38*deltat))","v":0.16666666666666666},null,null,{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(I$2,0,2*(BondMaturity-I$1)/deltat))/I$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-I$1,(1-EXP(-a*(BondMaturity-I$1)))/a)^2*IF(a=0,I$1/2,(1-EXP(-2*a*I$1))/(4*a))-IF(a=0,BondMaturity-I$1,(1-EXP(-a*(BondMaturity-I$1)))/a)*((I39*deltat+LN(K$3/I$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,I$1*deltat/2,(1-EXP(-2*a*I$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":89.982139635245616},{"$f":"$D38*deltaR+I$7","v":4.2729491924302837E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(K$2,0,2*(BondMaturity-K$1)/deltat))/K$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)^2*IF(a=0,K$1/2,(1-EXP(-2*a*K$1))/(4*a))-IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)*((K39*deltat+LN(M$3/K$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,K$1*deltat/2,(1-EXP(-2*a*K$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":93.905663077312639},{"$f":"$D38*deltaR+K$7","v":6.2879491924783898E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M39*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D38*deltaR+M$7","v":4.3129489640079427E-2}]
[null,null,null,null,{"$f":"IF(D38<jmax,0,1/6+(1/2)*(a^2*D38^2*deltat^2-a*D38*deltat))","v":0},null,null,null,{"$f":"payoff","v":0.65006129636381615},null,{"$f":"payoff","v":3.5265050827939746},null,{"$f":"payoff","v":7}]
[null,null,null,null,{"$f":"IF(D43<=jmin,1/6+(1/2)*(a^2*D43^2*deltat^2+a*D43*deltat),0)","v":0},null,null,null,null,null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($J$44-93,0)"},null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$44-93,0)"}]
[null,null,null,null,{"$f":"IF(D43<=jmin,-1/3-a^2*D43^2*deltat^2-2*a*D43*deltat,IF(D43<jmax,1/6+(1/2)*(a^2*D43^2*deltat^2-a*D43*deltat),0))","v":0.16666666666666666},null,null,null,null,null,{"$f":"IF(K$1>derivativematurity,0,IF(K$1=derivativematurity,K45,MAX((M32*$E41+M37*$E42+M42*$E43+M47*$E44+M52*$E45)*EXP(-K44*deltat),IF(AmerEur=\"American\",K45,-1E+307))))","v":4.7159776370114912},null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M45,MAX((O32*$E41+O37*$E42+O42*$E43+O47*$E44+O52*$E45)*EXP(-M44*deltat),IF(AmerEur=\"American\",M45,-1E+307))))","v":0}]
[null,null,null,{"$f":"D38-1","v":-2},{"$f":"IF(D43<=jmin,7/6+(1/2)*(a^2*D43^2*deltat^2+3*a*D43*deltat),IF(D43<jmax,2/3-a^2*D43^2*deltat^2,7/6+(1/2)*(a^2*D43^2*deltat^2-3*a*D43*deltat)))","v":0.66666666666666663},null,null,null,null,null,{"$f":"$E51*EXP(-I54*deltat)*I53+$E47*EXP(-I49*deltat)*I48+$E43*EXP(-I44*deltat)*I43+$E39*EXP(-I39*deltat)*I38+$E35*EXP(-I34*deltat)*I33","v":2.5572226203170366E-2},null,{"$f":"$E51*EXP(-K54*deltat)*K53+$E47*EXP(-K49*deltat)*K48+$E43*EXP(-K44*deltat)*K43+$E39*EXP(-K39*deltat)*K38+$E35*EXP(-K34*deltat)*K33","v":4.8032340033382831E-2}]
[null,null,null,null,{"$f":"IF(D43<=jmin,0,IF(D43<jmax,1/6+(1/2)*(a^2*D43^2*deltat^2+a*D43*deltat),-1/3-a^2*D43^2*deltat^2+2*a*D43*deltat))","v":0.16666666666666666},null,null,null,null,{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(K$2,0,2*(BondMaturity-K$1)/deltat))/K$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)^2*IF(a=0,K$1/2,(1-EXP(-2*a*K$1))/(4*a))-IF(a=0,BondMaturity-K$1,(1-EXP(-a*(BondMaturity-K$1)))/a)*((K44*deltat+LN(M$3/K$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,K$1*deltat/2,(1-EXP(-2*a*K$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":95.54632440051715},{"$f":"$D43*deltaR+K$7","v":4.5558983849095125E-2},{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M44*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D43*deltaR+M$7","v":2.5808981564390654E-2}]
[null,null,null,null,{"$f":"IF(D43<jmax,0,1/6+(1/2)*(a^2*D43^2*deltat^2-a*D43*deltat))","v":0},null,null,null,null,null,{"$f":"payoff","v":4.7159776370114912},null,{"$f":"payoff","v":7}]
[null,null,null,null,{"$f":"IF(D48<=jmin,1/6+(1/2)*(a^2*D48^2*deltat^2+a*D48*deltat),0)","v":0},null,null,null,null,null,null,null,{"$f":"SUBSTITUTE(SUBSTITUTE(formula,\"periodrate\",ADDRESS(ROW()+3,COLUMN())),\"bondprice\",ADDRESS(ROW()+3,COLUMN()-1))","v":"MAX($L$49-93,0)"}]
[null,null,null,null,{"$f":"IF(D48<=jmin,-1/3-a^2*D48^2*deltat^2-2*a*D48*deltat,IF(D48<jmax,1/6+(1/2)*(a^2*D48^2*deltat^2-a*D48*deltat),0))","v":0.16666666666666666},null,null,null,null,null,null,null,{"$f":"IF(M$1>derivativematurity,0,IF(M$1=derivativematurity,M50,MAX((O37*$E46+O42*$E47+O47*$E48+O52*$E49+O57*$E50)*EXP(-M49*deltat),IF(AmerEur=\"American\",M50,-1E+307))))","v":0}]
[null,null,null,{"$f":"D43-1","v":-3},{"$f":"IF(D48<=jmin,7/6+(1/2)*(a^2*D48^2*deltat^2+3*a*D48*deltat),IF(D48<jmax,2/3-a^2*D48^2*deltat^2,7/6+(1/2)*(a^2*D48^2*deltat^2-3*a*D48*deltat)))","v":0.66666666666666663},null,null,null,null,null,null,null,{"$f":"$E56*EXP(-K59*deltat)*K58+$E52*EXP(-K54*deltat)*K53+$E48*EXP(-K49*deltat)*K48+$E44*EXP(-K44*deltat)*K43+$E40*EXP(-K39*deltat)*K38","v":4.0722203674399835E-3}]
[null,null,null,null,{"$f":"IF(D48<=jmin,0,IF(D48<jmax,1/6+(1/2)*(a^2*D48^2*deltat^2+a*D48*deltat),-1/3-a^2*D48^2*deltat^2+2*a*D48*deltat))","v":0.16666666666666666},null,null,null,null,null,null,{"$f":"BondFaceValue*(EXP(-BondMaturity*OFFSET(M$2,0,2*(BondMaturity-M$1)/deltat))/M$3)*EXP(-(sigma^2)*IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)^2*IF(a=0,M$1/2,(1-EXP(-2*a*M$1))/(4*a))-IF(a=0,BondMaturity-M$1,(1-EXP(-a*(BondMaturity-M$1)))/a)*((M49*deltat+LN(O$3/M$3))/(IF(a=0,deltat,(1-EXP(-a*deltat))/a))-sigma^2*IF(a=0,M$1*deltat/2,(1-EXP(-2*a*M$1))*((1-EXP(-a*deltat))/a)/(4*a))))","v":100},{"$f":"$D48*deltaR+M$7","v":8.4884734887018809E-3}]
[null,null,null,null,{"$f":"IF(D48<jmax,0,1/6+(1/2)*(a^2*D48^2*deltat^2-a*D48*deltat))","v":0},null,null,null,null,null,null,null,{"$f":"payoff","v":7}]

{"sheet":"README","index":1,"n_rows":34,"n_cols":2,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
[null,"Notes (Important. Please Read)"]
[1,"This is a macro-enabled Excel file. To use its functionality, you must accept the security warning displayed and enable macros."]
[2,"The spreadsheet creates interest-rate tree to fit prices of zero coupon bonds. This requires trial and error. This is achieved"]
[null,"using circular references. If your spreadsheet displays warning of circular references (arrow signs connecting cells), then"]
[null,"please allow iterative calcualtions. Go to File menu, choose options, then formulas and make sure that the box near \"Enable"]
[null,"Iterative Calculations\" is checked. The value for number of iterations below that should be at least 50 and the maximum"]
[null,"error value should be low (I used 0.0001)."]
[3,"The inputs, the tree, and the output, all are in the \"Hull-White\" worksheet. The cells with blue text are input cells."]
[4,"Do not edit other cells. There are some cells with hidden formulas which are necessary for calculations."]
[5,"Enter values for sigma, a, and deltat (time step for each level of tree)."]
[6,"A tree with four levels and three time steps is created. You cannot use this spreadsheet for a tree with 5 levels and 4 time steps."]
[null," If you need a tree with three levels and two time steps, just ignore the last level of the tree."]
[7,"Input the zero rates (rates on zero coupon bonds), one for each level needed, in blue text cells at the top of the tree. Note that"]
[null,"cells are already percent formatted so to enter 5.386%, just type 5.386 and enter."]
[8,"This is all you need to do to create a tree."]
[9,"Each node has three numbers. Ignore the top number for now. The bottom number is the per period interest rate at that node."]
[null,"This is similar to short rate except that short rate is an instantaneous rate whereas this rate is a one period deposit from that"]
[null," node to the nodes in the next next level. The middle number is the state price of getting a dollar in that node. This means that"]
[null,"adding all the middle numbers in a level gives the price of a zero coupon bond paying one dollar at that time."]
[10,"The spreadsheet can price simple interest rate derivatives. A derivative can be priced if its payoff depends on the price of a"]
[null,"zero coupon bond or on the current per period rate. Enter the formula for payoff to the right of \"Derivative Payoff Formula\"."]
[11,"Input payoff formula as text of an Excel formula but do not put an equal sign in front. The formula may depend on the per period"]
[null," interest rate and/or bond price. Use the word periodrate for the per period rate. Use the word bondprice for the bond price. You"]
[null,"may also  use the word deltat for the time interval."]
[12,"Input the maturity of the derivative and specify whether it is European or American style."]
[13,"If the derivative payoff depends on the price of a bond, input the bond face value and bond maturity."]
[14,"Examples of derivative payoff functions:"]
[null,"Bond: bondprice"]
[null,"Call option on bond with strike price 90: MAX(bondprice-90,0)"]
[null,"Put option on bond with strike price 95: MAX(95-bondprice,0)"]
[null,"Caplet with cap rate of 5% on notional amount of $10,000 for a period: 10000*deltat*MAX(periodrate-.05,0)"]
[null,"Floorlet with floor rate of 4% on notional amount of $10,000 for a period: 10000*deltat*MAX(0.04-periodrate,0)"]
[15,"The derivative description mentioned at the bottom of column A is for your own convenience but is ignored in calculations."]
[16,"The Tree is created using the formula in the Hull derivatives textbook 8th edition."]